# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_wencai.py
#日期：2019-04-29
#备注：获取同花顺问财选股信息
CREATE TABLE `stock_wencai_chg` (
  `period_date` varchar(50) NOT NULL COMMENT '统计区间',
  `period_rank` int(4) NOT NULL COMMENT '涨幅排名',
  `period_chg` float NOT NULL DEFAULT '0' COMMENT '区间涨幅',
  `stock_no` varchar(20) NOT NULL,
  `stock_name` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`period_date`,`stock_no`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='股票区间涨幅统计';

CREATE TABLE `stock_wencai_pressure` (
  `trade_date` varchar(20) NOT NULL COMMENT '交易日期',
  `wc_pressure_price` float NOT NULL DEFAULT '0' COMMENT '压力价',
  `wc_support_price` float NOT NULL DEFAULT '0' COMMENT '支撑价',
  `wc_suggest` varchar(200) NOT NULL COMMENT '买入信息',
  `stock_no` varchar(20) NOT NULL,
  `stock_name` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`stock_no`,`trade_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='股票压力位和支撑位统计';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
from datetime import datetime
from pacong.stock.stock_config import db,cursor
from pycacho.cachobase.chinese_workday import get_trade_day
from pycacho.cachobase.logger import Logger
from pycacho.cachobase.deal_float import float_deal

logger = Logger("get_stock_wencai").get_log()

trade_date = get_trade_day(0,'s')            # 20200519
wc_date = '['+ trade_date +']'


headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.129 Safari/537.36",
    "Content-Type": "application/x-www-form-urlencoded"
}


def create_table():
    sql1 = 'delete from stock_wen_cai where trade_date=%s;' % (trade_date)
    cursor.execute(sql1)
    #cursor.execute(sql2)
    db.commit()

def createTable():
    sql1 = 'drop table if exists %s;'%(table)  # ,
    sql2 = 'create table %s (trade_date VARCHAR(20),stock_no VARCHAR(10),' \
           'stock_name VARCHAR(10),price_new float,price_chg float,price_max float,price_min float,price_change float,' \
           'price_amplitude float,price_today float,price_yesterday float,volume float,turnover float,turnover_rate float,' \
           'trailing_PE float,PB float,total_value float,market_value float,market_now VARCHAR(30),PRIMARY KEY (stock_no));'%(table)

    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()

# 1.压力位
# 2.区间涨幅
# count-股票数量，token-每次搜索都不一样，抓包；other不同搜索区别
def get_info(count,token,other,s_type):
    i = 0
    url = 'http://search.10jqka.com.cn/unified-wap/cache?page=1&perpage={0}&token={1}'.format(count,token)
    resp = requests.get(url, headers=headers)
    #num = resp.json()['data']['analyze_data']['total']
    data = resp.json()['data']['data']
    #print(data)
    for stock in data:
        #print(stock)
        wc_stock_no = stock['股票代码'].replace('.SZ','').replace('.SH','')
        wc_stock_name = stock['股票简称']
        if s_type == '压力位':
            wc_pressure_price = stock['止盈止损(压力位)' + wc_date]
            wc_support_price = stock['止盈止损(支撑位)' + wc_date]
            wc_suggest = stock['买入信号inter' + wc_date]
            #print(wc_pressure_price,wc_support_price,wc_suggest,wc_stock_no,wc_stock_name)
            pressure_support(wc_pressure_price,wc_support_price,wc_suggest,wc_stock_no,wc_stock_name)
        elif s_type == '区间涨幅':
            period_date = '前复权['+other[0]+'-'+other[1]+']'
            period_chg = stock['区间涨跌幅:'+period_date]
            if period_chg is not None:
                i = i + 1
                period_chg = round(float(period_chg)/100,2)
                wen_cai_chg(period_date,i,period_chg,wc_stock_no, wc_stock_name,)
        elif s_type == '历史收盘':
            date = trade_date1
            try:
                price_new = float_deal(stock['收盘价:前复权[' + date+']'])
                price_chg = float_deal(stock['涨跌幅:前复权[' + date+']'])
                price_max = float_deal(stock['最高价:前复权[' + date+']'])
                price_min = float_deal(stock['最低价:前复权[' + date+']'])
                price_change =float_deal(stock['涨跌[' + date+']'])
                price_amplitude =float_deal(stock['振幅[' + date+']'])
                price_today = float_deal(stock['开盘价:前复权[' + date + ']'])
                price_yesterday = 99                             # 股票昨天收盘价
                volume = 99                                      # 股票成交量 单位：手
                turnover = 99                                   # 股票成交额
                turnover_rate = float_deal(stock['换手率[' + date + ']'])       # 股票换手率 %
                trailing_PE = 99                                 # 股票动态市盈率
                PB = 99                                          # 市净率
                total_value = 99                                 # 股票总市值(元)
                market_value = 99                                # 股票流通值(元)
                market_now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 股票现价时间
                tradeInfo_to_mysql(table,date, wc_stock_no,wc_stock_name,price_new,price_chg,price_max,price_min,price_change,
                    price_amplitude,price_today,price_yesterday,volume,turnover,trailing_PE,turnover_rate,
                    PB,total_value,market_value,market_now)
            except:
                pass
        elif s_type == '解禁':
            #print(stock)
            wc_jj_date = stock["解禁日期"]
            wc_jj_rate = float_deal(stock["实际解禁比例"])
            wc_jj_cost = float_deal(stock["解禁成本"])
            wc_jj_price = float_deal(stock['收盘价:不复权' + wc_date])
            wc_jj_type = stock["解禁股类型"]
            wc_cost_rate = float_deal((wc_jj_price-wc_jj_cost)/wc_jj_cost)*100
            print(wc_stock_no,wc_stock_name,wc_jj_date,wc_jj_rate,wc_jj_cost,wc_jj_price,wc_jj_type,wc_cost_rate)


def pressure_support(wc_pressure_price,wc_support_price,wc_suggest,wc_stock_no,wc_stock_name):
    sql = "insert into stock_wencai_pressure (trade_date,wc_pressure_price,wc_support_price,wc_suggest,stock_no,stock_name,create_time) values " \
          "('%s','%s','%s','%s','%s','%s',now());" % (trade_date,wc_pressure_price,wc_support_price,wc_suggest,wc_stock_no,wc_stock_name)
    try:
        cursor.execute(sql)
    except Exception as e:
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('同花顺问财-->%s压力位信息插入成功！', wc_stock_no)


def wen_cai_chg(period_date, period_rank,period_chg,wc_stock_no,wc_stock_name):
    sql = "insert into stock_wencai_chg (period_date, period_rank,period_chg,stock_no,stock_name,create_time) values " \
          "('%s','%s','%s','%s','%s',now());" % (period_date, period_rank,period_chg,wc_stock_no,wc_stock_name)
    try:
        cursor.execute(sql)
    except Exception as e:
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('同花顺问财-->%s区间涨幅信息插入成功！', wc_stock_no)

# 将股票交易信息（股票每天交易价、交易量、涨跌、市值）
def tradeInfo_to_mysql(table,trade_date,stock_no, stock_name, price_new, price_chg, price_max, price_min, price_change, price_amplitude,
                       price_today, price_yesterday, volume, turnover, turnover_rate, trailing_PE, PB, total_value,
                       market_value, market_now):
    sql = "insert into %s (trade_date,stock_no,stock_name,price_new,price_chg,price_max,price_min," \
          "price_change,price_amplitude,price_today,price_yesterday,volume,turnover,turnover_rate,trailing_PE,PB," \
          "total_value,market_value,market_now) values" \
          "('%s','%s','%s','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%f','%s');" \
          % (table,trade_date,stock_no, stock_name, price_new, price_chg, price_max, price_min, price_change, price_amplitude,
             price_today, price_yesterday, volume, turnover, turnover_rate, trailing_PE, PB, total_value, market_value,
             market_now)
    try:
        cursor.execute(sql)
        db.commit()            # 事务提交
        logger.info('%s股票交易信息插入成功！',stock_no)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))

def merge():
    createTable()
    get_info(4500,'dc84b6382e7b87fa20d1da6e8c5fcf01','','历史收盘')
    sql1 = 'delete from stock_tradeinfo_total where trade_date=%s;' % (trade_date1)
    sql2 = 'insert into stock_tradeInfo_total(trade_date,stock_no,stock_name,price_new,price_chg,price_max,price_min,price_change,price_amplitude,price_today,price_yesterday,volume,turnover,turnover_rate,trailing_PE,PB,total_value,market_value,market_now) ' \
           'select trade_date,stock_no,stock_name,price_new,price_chg,price_max,price_min,price_change,price_amplitude,price_today,price_yesterday,volume,turnover,turnover_rate,trailing_PE,PB,total_value,market_value,market_now from %s where price_new>0;'%(table)
    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()
    logger.info("每天交易数据合并到总表stock_tradeinfo_total成功！")

if __name__ == '__main__':
    trade_date1 = '20210823'
    table = 'stock_tradeinfo_' + trade_date1
    #create_table()
    get_info(103,'507ba66b185e447540f8da5fd208e524','','解禁')
    #get_info(4195,'a2f80a0289a17134cf03c4d5bde07e77',['20190102','20191231'],'区间涨幅')
    #merge()


